@ECHO off
REM ***************************************************************************
REM **	File		:	ServiceLogicDB.bat
REM **	Name		:	ServiceLogicDB
REM **	Decription	:	This Script Creates HE Database and  all database Objects.
REM **	Author		:	Anil Kasalanati
REM **	Date		:	23-May-2007
REM ***************************************************************************
REM **	Syntax:	[drive:\path\] ServiceLogicDB [SERVER_NAME] [database_name]
REM **	[SERVER_NAME]	-	Required Parameter	:	server Name.
REM **	[database_name]	-	Required Parameter	:	database Name.
REM **	***********************************************************************
REM **	Error:	errflag =	0 For Success and -1 for Failure
REM **  ">>"	:	use in order to log the process in Log File 
REM **	'ECHO'	:	Used to Display Message on Console.
REM **	'REM'	:	Used to write Comment
REM ***************************************************************************


REM ** Assigning errorFlag to 

set ErrFlag=0

REM **	Declaring parameters for Server_Name,and Database_Name

SET server_name=%1

SET database_name=%2

REM **	Cheking Values for Parameter 
REM **	if Error occur pass the control to noParameters block

if "%1" == ""  goto :noparameters
if "%2" == ""  goto :noparameters

ECHO Start Changes for Server = [%server_name%] 

ECHO --- SCRIPT STARTED %date% %time% 
ECHO ********************************
ECHO --- SCRIPT STARTED %date% %time% >> ServiceLogicDB.log
ECHO ******************************** >> ServiceLogicDB.log

ECHO Command line: Builddb %1 %2
ECHO Installation server name: %computername% 
ECHO ****************************************

ECHO Command line: Builddb %1 %2 >> ServiceLogicDB.log
ECHO Installation server name: %computername%	>> ServiceLogicDB.log
ECHO ****************************************	>> ServiceLogicDB.log

ECHO --- Verifying database exist and Drop database %database_name% in server %computername%
ECHO ***************************************************************************************

ECHO --- Verifying database exist and Drop database %database_name% in server %computername% >> ServiceLogicDB.log
ECHO *************************************************************************************** >> ServiceLogicDB.log

osql /S%server_name% /d"master" /E /b /n /Q"IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'%database_name%')Drop Database %database_name%" >> SYNCASYNCDB.log
if errorlevel 1 set errflag=-1 & goto exit

ECHO --- Completed Verifying database exist and Drop database %database_name% in server %computername%
ECHO *************************************************************************************************

ECHO --- Completed Verifying database exist and Drop database %database_name% in server %computername%	>> ServiceLogicDB.log
ECHO *************************************************************************************************	>> ServiceLogicDB.log

ECHO ---Creating database %database_name% in server %computername%
ECHO *************************************************************

ECHO --- Creating database %database_name% in server %computername% >> ServiceLogicDB.log
ECHO ************************************************************* >> ServiceLogicDB.log

osql /S%server_name% /d"master" /E /b /n /Q"IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'%database_name%') Create Database %database_name%" >> ServiceLogicDB.log
if errorlevel 1 set errflag=-1 & goto exit

ECHO --- Database %database_name% Created in server %computername%
ECHO *************************************************************

ECHO --- Database %database_name% Created in server %computername%	>> ServiceLogicDB.log
ECHO *************************************************************	>> ServiceLogicDB.log

ECHO --- Creating Tables in database %database_name%
ECHO ***********************************************  

ECHO --- Creating Tables in database %database_name% 	>> ServiceLogicDB.log
ECHO ***********************************************	>> ServiceLogicDB.log

ECHO --- Creating table State Correlation.... >> ServiceLogicDB.log
ECHO -----------------------------------	>> ServiceLogicDB.log

osql /S%server_name% /d%database_name% /E /b /n /i ..\Tables\StateCorrelation.sql >> ServiceLogicDB.log 
IF errorlevel 1 SET errflag=-1 & GOTO exit

ECHO --- Creating table ServiceMaps.... >> ServiceLogicDB.log
ECHO -----------------------------------	>> ServiceLogicDB.log

osql /S%server_name% /d%database_name% /E /b /n /i ..\Tables\ServiceMaps.sql >> ServiceLogicDB.log 
IF errorlevel 1 SET errflag=-1 & GOTO exit

ECHO --- Creating table Dynamic Maps.... >> ServiceLogicDB.log
ECHO -----------------------------------	>> ServiceLogicDB.log

osql /S%server_name% /d%database_name% /E /b /n /i ..\Tables\DynamicMaps.sql >> ServiceLogicDB.log 
IF errorlevel 1 SET errflag=-1 & GOTO exit

ECHO --- Table Creation Completed For %database_name% 
ECHO ************************************************

ECHO --- Table Creation Completed For %database_name%	>> ServiceLogicDB.log
ECHO ************************************************	>> ServiceLogicDB.log

ECHO --- Creating Foreign Keys for tables in %database_name%	>> ServiceLogicDB.log
ECHO *******************************************************	>> ServiceLogicDB.log

ECHO --- Creating Foreign Key for table TransactionDetails... >> ServiceLogicDB.log
ECHO ------------------------------------------------------------------- >> ServiceLogicDB.log

ECHO --- Completed Foreign Keys creation for tables in %database_name% ----
ECHO ****************************************************************************************

ECHO --- Completed Foreign Keys creation for tables in %database_name%	>> ServiceLogicDB.log
ECHO *****************************************************************	 >> ServiceLogicDB.log

ECHO --- Inserting Master Data for %database_name%--------
ECHO *********************************************

ECHO --- Inserting Master Data for %database_name%	>> ServiceLogicDB.log
ECHO *********************************************	>> ServiceLogicDB.log

osql /S%server_name% /d%database_name% /E /b /n /i..\MasterData\ServiceLogicMasterData.sql >> ServiceLogicDB.log 
IF errorlevel 1 SET errflag=-1 & GOTO exit

ECHO --- Inserted Master Data for %database_name%	>> ServiceLogicDB.log
ECHO ********************************************	>> ServiceLogicDB.log


ECHO ***************************************************************
ECHO --------- Creating Stored Procedure in Database %database_name%

ECHO ***************************************************************  >> ServiceLogicDB.log
ECHO --------- Creating Stored Procedure in Database %database_name%	>> ServiceLogicDB.log

ECHO Creating Stored Procedure ps_d_Mid ... >> ServiceLogicDB.log
ECHO ----------------------------------------------------------- ServiceLogicDB.log
osql /S%server_name% /d%database_name% /E /b /n /i "..\Stored Procedures\ps_d_Mid.sql" >> ServiceLogicDB.log 
IF errorlevel 1 SET errflag=-1 & GOTO exit

ECHO Creating Stored Procedure ps_i_DynamicMaps ... >> ServiceLogicDB.log
ECHO ----------------------------------------------------------- >> ServiceLogicDB.log
osql /S%server_name% /d%database_name% /E /b /n /i "..\Stored Procedures\ps_i_DynamicMaps.sql" >> ServiceLogicDB.log 
IF errorlevel 1 SET errflag=-1 & GOTO exit

ECHO Creating Stored Procedure ps_i_InsertPayload ... >> ServiceLogicDB.log
ECHO ----------------------------------------------------------- >> ServiceLogicDB.log
osql /S%server_name% /d%database_name% /E /b /n /i "..\Stored Procedures\ps_i_InsertPayload.sql" >> ServiceLogicDB.log 
IF errorlevel 1 SET errflag=-1 & GOTO exit

ECHO Creating Stored Procedure ps_i_ServiceMaps ... >> ServiceLogicDB.log
ECHO ----------------------------------------------------------- >> ServiceLogicDB.log
osql /S%server_name% /d%database_name% /E /b /n /i "..\Stored Procedures\ps_i_ServiceMaps.sql" >> ServiceLogicDB.log 
IF errorlevel 1 SET errflag=-1 & GOTO exit

ECHO Creating Stored Procedure ps_s_GetDynamicMap ... >> ServiceLogicDB.log
ECHO ----------------------------------------------------------- >> ServiceLogicDB.log
osql /S%server_name% /d%database_name% /E /b /n /i "..\Stored Procedures\ps_s_GetDynamicMap.sql" >> ServiceLogicDB.log 
IF errorlevel 1 SET errflag=-1 & GOTO exit

ECHO Creating Stored Procedure ps_s_GetIOMap ... >> ServiceLogicDB.log
ECHO ----------------------------------------------------------- >> ServiceLogicDB.log
osql /S%server_name% /d%database_name% /E /b /n /i "..\Stored Procedures\ps_s_GetIOMap.sql" >> ServiceLogicDB.log 
IF errorlevel 1 SET errflag=-1 & GOTO exit

ECHO Creating Stored Procedure ps_s_GetMid ... >> ServiceLogicDB.log
ECHO ----------------------------------------------------------- >> ServiceLogicDB.log
osql /S%server_name% /d%database_name% /E /b /n /i "..\Stored Procedures\ps_s_GetMid.sql" >> ServiceLogicDB.log 
IF errorlevel 1 SET errflag=-1 & GOTO exit

ECHO Creating Stored Procedure ps_u_UpdateMid ... >> ServiceLogicDB.log
ECHO ----------------------------------------------------------- >> ServiceLogicDB.log
osql /S%server_name% /d%database_name% /E /b /n /i "..\Stored Procedures\ps_u_UpdateMid.sql" >> ServiceLogicDB.log 
IF errorlevel 1 SET errflag=-1 & GOTO exit


ECHO ----- Completed Stored Procedure creation in Database %database_name%
ECHO ---------------------------------------------------------------------

ECHO ----- Completed Stored Procedure creation in Database %database_name%	>> ServiceLogicDB.log
ECHO *********************************************************************	>> ServiceLogicDB.log

ECHO --- Setting Version for %database_name% ...	>> ServiceLogicDB.log
ECHO ------------------------------------------- >> ServiceLogicDB.log

REM osql /S%server_name% /d%database_name% /E /b /n /i "..\Extended Properties\ServiceLogicDBVersion.sql"	>> ServiceLogicDB.log
REM IF errorlevel 1 SET errflag=-1 & GOTO exit

ECHO --- SCRIPT FINISHED %date% %time%
ECHO ************************************

ECHO ** SCRIPT FINISHED %date% %time% >> ServiceLogicDB.log
ECHO *********************************** >> ServiceLogicDB.log

GOTO exit

REM ** noparameters block. When any parameter is missing.
:noparameters
	ECHO --------------------------------------------------------------------------
	ECHO All the parameters are required......
	ECHO Program exits as all parameters were not given.
	ECHO --------------------------------------------------------------------------
	GOTO end

REM ** Exit block. When any error occurs.
:exit
	IF %ErrFlag%==0 ECHO *** Builddb in %database_name% completed successfully *** 
	
	ECHO View the ServiceLogicDB.log for more details ************  
	
	IF %ErrFlag%==0 ECHO *** Builddb in %database_name% completed successfully ***   >> ServiceLogicDB.log  
	
	ECHO SCRIPT FINISHED %date% %time% >> ServiceLogicDB.log
	
	IF %ErrFlag%==-1 ECHO *** ERROR running Builddb in %database_name% Look Into Output Log File For Error Information

:end
popd
